|
Courses >
Computer Applications > Advanced Microsoft Excel 2003
 Any proficient Microsoft Excel user will agree that Excel is a very powerful analytical tool. Wouldn't it be great to learn how to effectively use every Excel tool at your disposal? In this course, you'll learn how to maximize Excel's analytical and functional capabilities.
Most organizations rely heavily on Microsoft Excel to consolidate, analyze, and report financial information. Your company is probably no exception. By learning these advanced techniques, you can become more of a value to your organization. Your ability to generate information with increased accuracy, timeliness, and usefulness will lead to better decision-making.
In this practical and information-packed course, you'll learn how to use the additional analytical tools provided by Excel add-ins. You'll become skilled in the use of validation to protect the integrity of your worksheets from other, less experienced users. Impress your coworkers by learning how to add functional and eye-catching controls to any worksheet, and find out how to use scenarios and data tables to quickly perform multiple what-if analyses. You'll discover advanced pivot table techniques, like creating calculated fields and calculated items. You'll become adept at consolidating and importing data, and you'll master the art of conditional formatting to highlight duplicate entries and other common worksheet problems. And you'll become proficient at nesting functions within other functions to accomplish just about anything Microsoft Excel has to offer.
This course includes a knowledgeable and caring instructor who will guide you through your lessons, facilitate discussions, and answer your questions. The instructor for this course will be Chad Wambolt.
 Chad Wambolt is a graduate of Boise State University, where he obtained his bachelor's degree in accounting. He has spent his entire professional career in the finance field, working for both privately and publicly held companies with sales ranging from $500 million to $3 billion. Through the course of his career, Chad has become an expert user of Microsoft Excel. His knowledge of Excel's capabilities has helped organizations streamline processes and save countless hours of labor. Chad has taught Excel to students of varying skill levels since 1997.
To purchase this course, click the Enroll Now button below:
Requirements:
Completion of Craig Power's online Intermediate Microsoft Excel 2003 course (or equivalent experience), Microsoft Windows Vista, XP or 2000, Microsoft Excel 2003 or Microsoft Office 2003 (Student and Teacher Edition, Standard Edition, Small Business Edition, or Professional Edition are all acceptable - please be sure to install this software on your computer before the course begins), Internet access, e-mail, the Microsoft Internet Explorer or Mozilla Firefox Web browser, and the Adobe Flash and PDF plug-ins (two free and simple downloads you obtain at http://www.adobe.com/downloads by clicking Get Adobe Flash Player and Get Adobe Reader). This course is not suitable for Macintosh users.
Microsoft Excel 2000 and 2002 users will be supported in the discussion area.
Syllabus:
All courses run for six weeks, with a two-week grace period at the end. Two lessons are released each week for the six-week duration of the course. You do not have to be present when the lesson is released, but you must complete each lesson within two weeks of its release.
A new section of each course starts on the second or third Wednesday of each month. If enrolling in a series of two or more courses, please be sure to space the start date for each course at least two months apart.
| Week One |
| Wednesday - Lesson 01 |
In our first lesson, you'll learn how to change some of the more obscure Excel options to help you use your time more efficiently. For instance, you'll learn how to set the default file location so that you can open and save files with little effort, and we'll talk about how to change the standard font, font size, and number of worksheet tabs in a new workbook. We'll go over how to create custom toolbars and drop-down menus so you can quickly access the tasks you use the most. Don't like the position of a command on an existing menu? Today, you'll find out how to move it up or down on the list. By the time you finish this lesson, you'll be extremely comfortable with Excel's behind-the-scenes set-up options and all the customizations that are available to you.
|
| Friday - Lesson 02 |
Excel is a wonderful tool that you can use to analyze fairly large amounts of data. This information is normally stored in Excel as a table. In today's lesson, you'll learn how to use AutoFilter, Sorting, and data forms to effectively manage these tables. A data form is a commonly overlooked tool that can help eliminate the redundancy of data entry. You'll learn how to use a data form to perform such table management tasks as editing, deleting, restoring, and searching for table records. You'll even learn how to use multiple search criteria to create a truly focused search.
|
| Week Two |
| Wednesday - Lesson 03 |
In an office environment, workbooks are often shared and updated by multiple users. This sharing can lead to integrity issues with the captured data. In today's lesson, we'll explore how to use data validation to minimize the risk of receiving incorrect information. You can apply data validation to almost any situation to validate numbers, dates, text length, and values from a list. We'll even take it one step further and by creating custom validation rules. You'll also learn how to create valuable input and error messages to further aid the user.
|
| Friday - Lesson 04 |
In today's lesson, you'll learn how create functional and eye-catching custom controls. What are custom controls? They're drop-down boxes, check boxes, or option buttons that you can add to any workbook. Wouldn't it be useful to control the values in one list box based on the value selected in the first list box? You'll find out just how easy it is when I show you how to create dynamic list boxes. After this lesson, your command of creating custom controls will surely impress your coworkers.
|
| Week Three |
| Wednesday - Lesson 05 |
There's more to conditional formatting than just formatting based on cell values, and we'll explore all of Excel's conditional formatting options in this lesson. Formula conditional formatting is a powerful tool that you can use to make your life easier. Today, we'll discuss how to use formula conditional formatting to identify all the cells in your workbook that contain formulas. Then you'll discover how to use formula conditional formatting to hide error values in cells, highlight duplicate values in a list, compare values in two lists, and create a very useful scorecard to identify problem areas. The potential uses are nearly limitless!
|
| Friday - Lesson 06 |
Today's lesson is all about data consolidation. First we'll discuss how to consolidate information from within the same workbook using category labels to filter the data just the way you want it. Then you'll discover how to consolidate data from multiple workbooks without even opening the external workbooks. We'll explore using wildcards in the consolidation process to further control the data you're consolidating. Then you'll get the chance to try out Excel's automatic outlining feature to display different summarization levels of information. If you're responsible for gathering information using Excel, you won't want to miss this lesson.
|
| Week Four |
| Wednesday - Lesson 07 |
In this lesson, you'll discover how useful Excel's built-in functions can be. I'll show you how to take a couple basic functions and "nest" them together to form a very powerful combined function. You'll learn how to use the various database functions and how they differ from normal functions. We'll then explore using the conditional sum wizard add-in to perform a calculation similar to a DSUM or SUMIF function. Last, you'll discover practical uses for the VLOOKUP function and wonder how you ever lived without it.
|
| Friday - Lesson 08 |
There are many ways to bring data into your Excel workbooks. Today, you'll learn how to use the data import feature to quickly and efficiently import data from most any outside source. We'll start by bringing in an entire workbook tab from another workbook. Then, you'll discover how easy it is to import data from an external database like Microsoft Access. The best part about these imports is that you can set them up to refresh the data at regular intervals so you always have the latest and greatest information from the source. We'll end the lesson with some practice using one of Excel 2003's new features, called a Web query.
|
| Week Five |
| Wednesday - Lesson 09 |
Data tables are a powerful but often overlooked "what-if" analysis tool. This is probably because most people don't understand how simple and useful data tables can be. Imagine being able to see all the possible results of a calculation by changing the inputs to the calculation? In today's lesson, I'll show you how to use a one-variable and two-variable data table to accomplish this. And after this lesson, you'll be extremely comfortable adding this "what-if" analysis tool to your arsenal.
|
| Friday - Lesson 10 |
This lesson is packed with useful information on how to use three of Excel's powerful "what-if" analysis tools. We'll discuss how to use goal seek to calculate one of the input cells when you know the desired result of the calculation. You'll learn how to use scenarios to quickly calculate and compare information. Then you'll discover how to use the solver tool to find the optimal solution to a problem by using different constraint parameters. If you're responsible for analyzing data, this lesson was designed with you in mind.
|
| Week Six |
| Wednesday - Lesson 11 |
I can't think of a more powerful analytical tool than a pivot table. A pivot table allows you to see different views of information instantaneously by simply adding, removing, or changing an element within the table. We'll go beyond just creating a pivot table in this lesson. You'll also learn how to create a calculated field and calculated item within the table. Being able to create your own calculations within the table is where Excel's pivot table becomes an exceptional tool. I'll also teach you how to take the results of your pivot table and create an interactive pivot chart.
|
| Friday - Lesson 12 |
In our final lesson, you'll discover some great tools that you can access just by installing the Analysis ToolPak Add-in. With this add-in, you can easily calculate a moving average or use its sampling techniques. We'll explore using the advanced filter for those situations when the regular auto filter isn't sophisticated enough. You'll finish the course by learning about some of the new features available in Excel 2003, including advancements in list management, comparing workbooks side by side, and using a watch window to see how calculations elsewhere change when you change an input cell.
|
To purchase this course, click the Enroll Now button below:
Student Reviews:
"Chad, Just wanted to thank you for an excellent course! Many thanks for all your time and effort in creating the workbook and examples that we used throughout the class. I will be referring back to it many times as I try and incorporate many of the advanced features of Excel 2003 in my job."
"Enjoyed this course very very much! Found it to be well thought out, easy to follow, and extremely useful. I learned a lot of brand NEW things I had never done and was able to add to what I already know! BRAVO. I would recommend this course to others. Thanks."
"I cannot tell you how much I enjoyed your class! I am such a novice at computers, but you brought the class studies down to the level that even I could understand it! I would recommend you to anyone. Thanks!"
"I found this course extremely helpful; the best on-line course I have taken to date."
"I was able to pick up quite a number of new functions that I can use within my job. I will use the notes and lessons as a reference when I need to use some of the other capabilites of Excel. Good class materials."
"Thanks very much! My schedule got very crazy but I was able to complete the course. I found that the questions asked by others and the responses from the Instructor and other students helped me greatly!"
"The Supplementary Material section was very helpful. The assignments where top notch. I have not used Excel to the depths that this class was taught but I could follow the lesson with no problem. A very worthwhile class."
"This class was excellent! I had no idea that Excel could do so much. Hopefully this new knowledge will help me advance my career. I would be very interested in taking an Excel Formulas and Functions class from you. You are a very effective instructor."
"This course was very helpful to my use of Excel. I'm already incorporating many of the time-saving tips in my work. The course has shown me many powerful tools of Excel that I was not using. Excellent job, Chad!"
"This was a great course, and it has already improved my Excel skills tremendously."
To purchase this course, click the Enroll Now button below:
Recommended Courses:
Students who enrolled in Advanced Microsoft Excel 2003 were also interested in the following courses:
Accounting Fundamentals
Introduction to Business Analysis
Introduction to Microsoft Excel 2003
Intermediate Microsoft Excel 2003
Introduction to QuickBooks 2007
Intermediate Microsoft Excel 2007
|
